// 加载Express模块
const express = require('express');

// 加载MySQL模块
const mysql = require('mysql');

// 加载bodyParser模块
const bodyParser = require('body-parser');

// 加载获取当前时间依赖
const sd = require('silly-datetime');


// 创建MySQL连接池
const pool = mysql.createPool({
  host: '127.0.0.1',   //MySQL服务器地址
  port: 3306,          //MySQL服务器端口号
  user: 'root',        //数据库用户的用户名
  password: '',        //数据库用户密码
  database: 'lemonshop',    //数据库名称
  connectionLimit: 20, //最大连接数
  charset: 'utf8'      //数据库服务器的编码方式
});

// 创建服务器对象
const server = express();

server.use(bodyParser.urlencoded({
  extended: false
}));


// 加载CORS模块
const cors = require('cors');
const { query } = require('express');

// 使用CORS中间件
server.use(cors({
  origin: ['http://localhost:8080','http://localhost:8081', 'http://127.0.0.1:8080','http://localhost:8082','http://localhost:8083','http://localhost:8084','http://localhost:8085']
}));

//用户注册接口
server.post('/register', (req, res) => {
  // 获取当前时间
  const time = sd.format(new Date(), 'YYYY-MM-DD HH:mm');
  console.log(time);
  // 2022-01-07 11:00
 
  // 获取用户名和密码信息
  let uname = req.query.UserName;
  let pwd = req.query.password;
  //以username为条件进行查找操作，以保证用户名的唯一性
  let sql = 'SELECT * from userinfo WHERE UserName=?';
  pool.query(sql, [uname], (error, results) => {
    if (error) throw error;
    let count = results.length;
    if (count == 0) {
      // 将用户的相关信息插入到数据表
      sql = `INSERT INTO userinfo VALUES(null,'${uname}','   ${pwd}','${uname}',"快乐每一天",2,'${time}')`;
      pool.query(sql, (error, results) => {
        if (error) throw error;
        res.send({ message: '注册成功', code: 200 });
      })
    } else {
      res.send({ message: '用户名重复', code: 201 });
    }
  });
});

// 用户登录接口
server.post('/login', (req, res) => {
  //获取用户名和密码信息
  let uname = req.query.UserName;
  let upwd = req.query.password;
  // SQL语句
//以username为条件进行查找操作，以保证用户名的唯一性
let sql = 'SELECT * from userinfo WHERE UserName=?';
pool.query(sql, [uname], (error, results) => {
  if (error) throw error;
  let count = results.length;
  if (count == 0) {
    res.send({ message: '用户还未注册', code: 201 });
  } else {
      // 将用户的相关信息插入到数据表
      sql = `SELECT * from userinfo WHERE UserName='${uname}'ANd UserPwd='${upwd}'`;
      pool.query(sql, (error, results) => {
        if (error) throw error;
        let a=results.length;
        if(a==0){
          res.send({message: '密码错误', code: 202})
        }else{
          res.send({ message: '登录成功', code: 200,results:results});
        }
      })
  }
}); 
});

// 获取用户信息内容
server.get('/GetUser', (req, res) => {
  //获取用户名和密码信息
  let muser = req.query.UserName;
  // SQL语句
  let sql = `select * from userinfo where UserName='${muser}'`;
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({message:'ok',code:200,result:results[0]});
  });
});



// 用户修改昵称、密码、个性签名
server.post('/SetUserInfo', (req, res) => {
  var UserName=req.query.UserName
  var UserNickName=req.query.UserNickName
  var UserSignature=req.query.UserSignature
  var UserPassword=req.query.UserPassword
  var sql = `update userinfo set UserNickName='${UserNickName}',UserSignature="${UserSignature}",UserPwd="${UserPassword}" WHERE UserName='${UserName}'`;
  pool.query(sql,(error, result) => {
    if (error) throw error;
    res.send({ message: '修改成功', code: 200});
  });
});

// 用户设置性别
server.get("/SetSex",(req,res)=>{
  var uname=req.query.UserName
  console.log(uname)
  var sex=req.query.UserSex
  var sql = `update userinfo set UserSex=${sex} WHERE UserName='${uname}'`;
  pool.query(sql,(error,result)=>{
    if (error) throw error;
    res.send({ message: '选择成功', code: 200});
  })
})


  // 用户获取首页商品信息
  server.get("/GetProductTitle",(req,res)=>{
    var sql = `select * from products WHERE ProductQu=10`;
    pool.query(sql,(error,NewProduct)=>{
    var sql = `select * from products WHERE ProductQu=20`;
      if (error) throw error;
      pool.query(sql,(error,HotProduct)=>{
    var sql = `select * from products WHERE ProductQu=30`;
        if (error) throw error;
        pool.query(sql,(error,LatestProduct)=>{
          if (error) throw error;
          res.send({ message: 'ok', code: 200,result:{NewProduct,HotProduct,LatestProduct}});
        })
      })
    })
  })

  // 获取商品详细信息
  server.get("/GetProductDetails",(req,res)=>{
    var pid=req.query.pid
    var sql = `select * from products where pid='${pid}'`;
    pool.query(sql,(error,result)=>{
      if (error) throw error;
      res.send({ message: 'ok', code: 200,result:result});
    })
  })

  //新建用户地址信息
  server.post("/AddAdress",(req,res)=>{
    var uid=req.query.uid
    var Name=req.query.Name
    var Phone=req.query.Phone
    var myAddressProvince=req.query.myAddressProvince
    var myAddressCity=req.query.myAddressCity
    var myAddresscount=req.query.myAddresscounty
    var AdressDetail=req.query.AdressDetail
    var value=req.query.value
    // console.log("value的值为",value)
    if(value==0){
      //设为默认地址，则先查询是否有默认地址，有就先将之前的取消，再插入，没有则直接插入
      var sql=`select * from useradress where IsAdress=0 and UserId='${uid}'`
      pool.query(sql,(error,result)=>{
         if (error) throw error;
         let count = result.length;
        //  console.log("返回的数据",count)
         if (count == 0) {
          // 没有默认地址
          var sql=`INSERT INTO useradress VALUES(null,'${Name}','${Phone}','${myAddressProvince}','${myAddressCity}','${myAddresscount}','${AdressDetail}','${value}',"${uid}")`
          pool.query(sql,(error,result)=>{
            if (error) throw error;
            res.send({ message: '设置默认地址，但之前无默认地址，直接插入', code: 200});
          })
         }else{
          var sql=`update useradress set IsAdress=1 WHERE UserId='${uid}'`
          pool.query(sql,(error,result)=>{
            if (error) throw error;
            var sql=`INSERT INTO useradress VALUES(null,'${Name}','${Phone}','${myAddressProvince}','${myAddressCity}','${myAddresscount}','${AdressDetail}','${value}',"${uid}")`
            pool.query(sql,(error,result)=>{
              if (error) throw error;
              res.send({ message: '设置默认地址，之前有默认地址，修改后插入', code: 200,});
            })
          })
         }
      })
    }else{
      //不设置为默认地址 则直接插入
      var sql=`INSERT INTO useradress VALUES(null,'${Name}','${Phone}','${myAddressProvince}','${myAddressCity}','${myAddresscount}','${AdressDetail}',${value},"${uid}")`
      pool.query(sql,(error,result)=>{
        if (error) throw error;
        res.send({ message: '不设置默认地址，直接插入', code: 200});
      })
     }
  })

  //编辑用户地址信息
  server.post("/EditAdress",(req,res)=>{
    var Aid=req.query.Aid
    var uid=req.query.uid
    console.log("前台传入的用户id为：",uid)
    console.log("前台传入的Aid为：",Aid)
    var Name=req.query.Name
    var Phone=req.query.Phone
    var myAddressProvince=req.query.myAddressProvince
    var myAddressCity=req.query.myAddressCity
    var myAddresscount=req.query.myAddresscounty
    var AdressDetail=req.query.AdressDetail
    var value=req.query.value
    console.log("前台传入的用户id为：",value)
    // console.log("value的值为",value)

    if(value==0){
      //设为默认地址，则先查询是否有默认地址，有就先将之前的取消，再插入，没有则直接插入
      var sql=`select * from useradress where IsAdress=0 and UserId='${uid}'`
      pool.query(sql,(error,result)=>{
         if (error) throw error;
         let count = result.length;
        //  console.log("返回的数据",count)
         if (count == 0) {
          // 没有默认地址
          var sql=`update useradress set Name='${Name}',Phone='${Phone}',Province='${myAddressProvince}',city='${myAddressCity}',District='${myAddresscount}',AdressDetail='${AdressDetail}',IsAdress=${value} where Aid=${Aid} and UserId=${uid}`
          console.log(sql)
          pool.query(sql,(error,result)=>{
            if (error) throw error;
            res.send({ message: '设置默认地址，但之前无默认地址，直接更新其他内容', code: 200});
          })
         }else{
          var sql=`update useradress set IsAdress=1 WHERE UserId='${uid}'`
          pool.query(sql,(error,result)=>{
            if (error) throw error;
            var sql=`update useradress set Name='${Name}',Phone='${Phone}',Province='${myAddressProvince}',city='${myAddressCity}',District='${myAddresscount}',AdressDetail='${AdressDetail}',IsAdress=${value} where Aid=${Aid} and UserId=${uid}`
            console.log(sql)
            pool.query(sql,(error,result)=>{
              if (error) throw error;
              res.send({ message: '设置默认地址，之前有默认地址，修改后更新', code: 200,});
            })
          })
         }
      })
    }else{
      //不设置为默认地址 则直接更新
      var sql=` update useradress set Name='${Name}',Phone='${Phone}',Province='${myAddressProvince}',city='${myAddressCity}',District='${myAddresscount}',AdressDetail='${AdressDetail}',IsAdress=${value} where Aid=${Aid} and UserId=${uid}`
      console.log(sql)
      pool.query(sql,(error,result)=>{
        if (error) throw error;
        res.send({ message: '不设置默认地址，更新其他内容', code: 200});
      })
     }
  })

  //获取用户地址信息
  server.get("/GetUserAdress",(req,res)=>{
    var uid=req.query.uid
    var sql = `select * from useradress where UserId='${uid}'`;
    pool.query(sql,(error,result)=>{
      if (error) throw error;
      res.send({ message: 'ok', code: 200,result:result});
    })
  })

  //获取设置用户地址信息
  server.get("/GetEditAdress",(req,res)=>{
    var Aid=req.query.Aid
    var sql = `select * from useradress where Aid='${Aid}'`;
    pool.query(sql,(error,result)=>{
      if (error) throw error;
      res.send({ message: 'ok', code: 200,result:result});
    })
  })

  //删除用户地址信息
  server.get("/DeleteAdress",(req,res)=>{
    var Aid=req.query.Aid
    var sql = `delete from useradress where Aid=${Aid}`;
    pool.query(sql,(error,result)=>{
      if (error) throw error;
      res.send({ message: 'ok', code: 200,result:result});
    })
  })


  //获取用户的订单信息
  server.get("/GetOrder",(req,res)=>{
    var uid=req.query.Uid
    var Status=req.query.Status
    // console.log("请求状态为",Status)
    if(Status==1){
      var sql = `select * from ordere where UserID='${uid}' order by oid DESC`;
    }else{
      var sql = `select * from ordere where UserID='${uid}' and Status=${Status} order by oid desc`;

    }
    // console.log(sql)
    pool.query(sql,(error,result)=>{
      if (error) throw error;
      var sql = `select * from orderitem`
      pool.query(sql,(error,results)=>{
      if (error) throw error;
        res.send({ message: 'ok', code: 200,results:results,order:result});

      })
    })
  })

  //获取用户的订单详情信息
  server.get("/GetOrderDetail",(req,res)=>{
    var oid=req.query.oid
    var sql = `select * from ordere where oid='${oid}'`;
    pool.query(sql,(error,result)=>{
      if (error) throw error;
      var sql = `select * from orderitem where OrderId = '${oid}'`
      pool.query(sql,(error,results)=>{
      if (error) throw error;
        res.send({ message: 'ok', code: 200,results:results,order:result});

      })
    })
  })

//用户取消订单
server.get("/SetOrderDetail",(req,res)=>{
  var oid=req.query.oid
  var sql = `update ordere set Status=6 WHERE oid=${oid}`;
  pool.query(sql,(error,result)=>{
    if (error) throw error
      res.send({ message: 'ok', code: 200});
    })
})

//用户支付订单
server.get("/SetOrderDetails",(req,res)=>{
  var oid=req.query.oid
  var sql = `update ordere set Status=3 WHERE oid=${oid}`;
  pool.query(sql,(error,result)=>{
    if (error) throw error
      res.send({ message: 'ok', code: 200});
    })
})

//获取用户默认地址
server.get("/GetUserIsAdress",(req,res)=>{
  var uid=req.query.uid
  var IsAdress=req.query.IsAdress
  var sql = `select * from useradress where UserId=${uid} and IsAdress=${IsAdress}`;
  pool.query(sql,(error,result)=>{
    if (error) throw error
      res.send({ message: 'ok', result:result});
    })
})
//获取用户购物车商品
server.get("/GetUserCart",(req,res)=>{
  var uid=req.query.uid
  // 
  var sql = `select  * from productcart where UserId=${uid}`;
  pool.query(sql,(error,result)=>{
    if (error) throw error
      res.send({ message: 'ok', result:result});
    })
})

//设置用户购物车商品数量
server.get("/SetUserCart",(req,res)=>{
  var pid=req.query.pid
  var sum=req.query.sum
  var sql = `update productcart set ProductSum=${sum} WHERE pid=${pid}`;
  pool.query(sql,(error,result)=>{
    if (error) throw error
      res.send({ message: 'ok', result:result});
    })
})

//设置用户购物车商品是否被选中
server.get("/SetUserCartIs",(req,res)=>{
  var check=req.query.result
  console.log("选中序号",check)
  
  var sql = `update productcart set IsCheck=1`;
  if(check==""){
  console.log("设置所以为不选中")
    pool.query(sql,(error,result)=>{
      if (error) throw error
      res.send({ message: 'ok', result:result});
    })
  }else{
  console.log("正常运行")
    pool.query(sql,(error,result)=>{
      if (error) throw error
    var sql = `update productcart set IsCheck=0 where pid in (${check})`;
      pool.query(sql,(error,results)=>{
        if (error) throw error
         res.send({ message: 'ok', result:result});
      })
      })
  }
 
})


//添加用户购物车商品
server.get("/AddProductCart",(req,res)=>{
  var ProductId=req.query.pid
  var ProductImg=req.query.ProductImg
  var ProductTitle=req.query.ProductTitle
  var ProductPrice=req.query.ProductPrice
  var UserId=req.query.UserId
  var sql = `SELECT * FROM productcart where ProductId=${ProductId} and UserId =${UserId}`;
  pool.query(sql, (error, results) => {
    if (error) throw error;
    let count = results.length;
    if (count == 0) {
      // 将商品的相关信息插入到数据表
      var sql = `INSERT INTO productcart VALUES(null,'${ProductId}','${ProductImg}','${ProductTitle}',"${ProductPrice}",1,0,${UserId})`;
      pool.query(sql, (error, results) => {
        if (error) throw error;
        res.send({ message: '添加成功', code: 200 });
      })
    } else {
      res.send({ message: '商品已经存在购物车', code: 201 });
    }
  });
});

// 获取结算的商品
server.get("/GetProductshopitem",(req,res)=>{
  var check=req.query.check
  var number=req.query.number
  var a=""
  var sql = `select oid from ordere where OrderNumber="${number}"`;
  pool.query(sql,(error,resuu)=>{
    if (error) throw error
    console.log("订单id为",resuu)
    res.send({message:"下单成功",results:resuu[0].oid})
    var sql = `select  * from productcart where pid in (${check})`;
  pool.query(sql,(error,result)=>{
    if (error) throw error
      // console.log(result)
      for (let i = 0; i < result.length; i++) {
        var sql = `INSERT INTO orderitem VALUES(null,'${result[i].ProductId}','${resuu[0].oid}',"${result[i].ProductSum}",'${result[i].ProductTitle}','${result[i].ProductPrice}','${result[i].ProductImg}')`
        // 执行添加订单中商品语句
        pool.query(sql,(error,results)=>{
          if (error) throw error
          // console.log(results.insertId)
        })
        var sql=`delete from productcart where pid=${result[i].pid }`
        // 执行删除购物车中商品语句
        pool.query(sql,(error,results)=>{
          if (error) throw error
        })
        
      }
    })
  })
  
  
})


// 添加订单
server.get("/AddOrder",(req,res)=>{
  var totle=req.query.totle
  var time=req.query.time
  var number=req.query.Number
  var Uid=req.query.userid
  var sql = `INSERT INTO ordere VALUES(null,'${totle}',2,'${time}','${number}',"${Uid}",'普通快递')`;

  pool.query(sql,(error,result)=>{
    if (error) throw error
      res.send({ message: 'ok', result:result});
    })
})

// 获取订单商品
server.get("/GetOrderShop",(req,res)=>{
  var oid=req.query.oid
  var sql = `select * from orderitem where OrderId=${oid}`;

  pool.query(sql,(error,result)=>{
    if (error) throw error
      res.send({ message: 'ok', result:result});
    })
})
// 指定服务器对象监听的端口号
server.listen(3000, () => {
  console.log('服务器跑起来了');
});